import requests
from bs4 import BeautifulSoup
import re
import pymysql

#抓取球队信息
class NbaNew:
    def __init__(self):
        self.header=self.header={
              'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/99.0.4844.82 Safari/537.36'
        }
        self.table_name="nba_info"
        self.sql = """insert into nba_info(rank,name,win,fail,winpre,winandlose) values ("{0}","{1}","{2}","{3}","{4}","{5}")"""


    #获取响应内容
    def get_source(self,url):
        response=requests.get(url)
        if response.status_code==200:
            soup=BeautifulSoup(response.text,'lxml')
        else:
            print('HTTP请求错误..'+url)
            soup=None
        return soup
    #解析内容
    def get_time_info(self,soup):
        team_info=[]
        table=soup.find(class_="players_table")
        #html表格的所有列
        for row in table.find_all('tr'):
            if "class" not in row.attrs:
                cols=row.find_all("td")
                #排名、队名、胜、负、胜率、连胜/负
                team_info.append([cols[0].text,cols[1].text,cols[2].text,
                                  cols[3].text,cols[4].text,cols[13].text])
        #由于没有区分东部西部，因此更改索引插入数据库中，按道理应该创建两张表的，
        #但是这里意在获取数据而已。
        for i in range(len(team_info)):
                #向数据库中插入数据
            sql1 = self.sql.format(i+1, team_info[i][1], team_info[i][2], team_info[i][3],
                                       team_info[i][4],team_info[i][5])

            try:
                # 检查连接是否存在，断开的话会重
                self.db.ping(reconnect=True)
                self.con.execute(sql1)
                self.db.commit()  # 确认交易
                print("新增一笔记录")
            except:
                self.db.rollback()
                print('新增记录失败')
            self.db.close()


    #检查表的存在
    def table_exist(self):
        # 判断表的存在
        sql = "show tables;"
        self.con.execute(sql)
        tables = [self.con.fetchall()]
        table_list = re.findall('(\'.*?\')', str(tables))
        table_list = [re.sub("'", '', each) for each in table_list]
        if self.table_name in table_list:
            # 存在返回1
            return 1
        else:
            return 0


    def create_table(self):
        # #创建数据库连接
        self.db = pymysql.connect("localhost", "root", '123456', "mql", charset='utf8')
        # 创建cursor对象
        self.con = self.db.cursor()
        if self.table_exist()==0:
            # 排名、队名、胜、负、胜率、连胜 / 负
            sqll = """create table nba_info
                            (rank char(20) primary key  not null,
                              name text not null,
                              win char(20),
                              fail char(20),
                              winpre char(20),
                              winandlose char(20));"""
            self.con.execute(sqll)
            self.db.commit()
            print("创建成功")
        else:
            pass





if  __name__=='__main__':
    url='https://nba.hupu.com/standings'
    mql=NbaNew()
    soup=mql.get_source(url)
    mql.create_table()
    mql.get_time_info(soup)

